package com.saishi.dao.impl;

import com.saishi.dao.UserDao;
import com.saishi.entity.Classes;
import com.saishi.entity.Question;
import com.saishi.entity.User;
import com.saishi.entity.classuser;
import com.saishi.entity.uclass;
import com.saishi.util.AliPool;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.Date;

public class UserDaoImpl implements UserDao {
    QueryRunner runner = new QueryRunner();
    PreparedStatement pst;
    @Override
    public List<User> Login(String user, String pass,String tel,String emile) throws Exception {
        String sql = "select * from `user` where (uusername=? and upass=?) or (unumber=? and upass=?) or (uemile=? and upass=?)";
        List<User> list = new ArrayList<User>();
        Connection conn = AliPool.getConn();
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1,user);
        pst.setString(2, pass);
        pst.setString(3,tel);
        pst.setString(4, pass);
        pst.setString(5,emile);
        pst.setString(6, pass);
        ResultSet rs = pst.executeQuery();
        while(rs.next()){
            int uid = rs.getInt("uid");
            String username = rs.getString("uusername");
            String upass = rs.getString("upass");
            String uname = rs.getString("uname");
            String unumber = rs.getString("unumber");
            String uemile = rs.getString("uemile");
            String ugender = rs.getString("ugender");
            int uage = rs.getInt("uage");
            String uprofession = rs.getString("uprofession");
            String uaddress = rs.getString("uaddress");
            String ustate = rs.getString("ustate");
            String uheadx = rs.getString("uheadx");
            User list2 = new User(uid,username,upass,uname,unumber,uemile,ugender,uage,uprofession,uaddress,ustate,uheadx);
            list.add(list2);
        }
        conn.close();
        return list;
    }

    @Override
    public List add(int uid,String uusername,String tel, String pass) throws SQLException {
        String sql = "insert into `user`(uid,uusername,upass,unumber) values(?,?,?,?)";
        Connection conn = AliPool.getConn();
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setInt(1, uid);
        pst.setString(2, uusername);
        pst.setString(3, pass);
        pst.setString(4,tel);
        int rs = pst.executeUpdate();
        conn.close();
        return null;
    }

    @Override
    public List<User> seuserlist(String usern) throws Exception {
        String sql = "select * from `user` where uusername=? or unumber=? or uemile=?";
        List<User> list = new ArrayList<User>();
        Connection conn = AliPool.getConn();
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1,usern);
        ResultSet rs = pst.executeQuery();
        while(rs.next()){
            int uid = rs.getInt("uid");
            String username = rs.getString("uusername");
            String upass = rs.getString("upass");
            String uname = rs.getString("uname");
            String unumber = rs.getString("unumber");
            String uemile = rs.getString("uemile");
            String ugender = rs.getString("ugender");
            int uage = rs.getInt("uage");
            String uprofession = rs.getString("uprofession");
            String uaddress = rs.getString("uaddress");
            String ustate = rs.getString("ustate");
            String uheadx = rs.getString("uheadx");
            User list2 = new User(uid,username,upass,uname,unumber,uemile,ugender,uage,uprofession,uaddress,ustate,uheadx);
            list.add(list2);
        }
        conn.close();
        return list;
    }

    @Override
    public int updayhxx(String nic,String sex,int nage,String zhiye,String address,String email,String username) throws SQLException {
        String sql = "update `user` set uname=?,ugender=?,uage=?,uprofession=?,uaddress=?,uemile=? where uusername=?";
        Connection conn = AliPool.getConn();
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, nic);
        pst.setString(2, sex);
        pst.setInt(3, nage);
        pst.setString(4, zhiye);
        pst.setString(5, address);
        pst.setString(6, email);
        pst.setString(7,username);
        int rs = pst.executeUpdate();
        conn.close();
        return 1;
    }

    @Override
    public int upuserpass(String newpass,String username) throws SQLException {
        String sql = "update `user` set upass=? where uusername=?";
        Connection conn = AliPool.getConn();
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, newpass);
        pst.setString(2, username);
        int rs = pst.executeUpdate();
        conn.close();
        return 1;
    }

    @Override
    public List<User> allQuery() throws SQLException {
        String sql = "select * from user";
        Connection conn=AliPool.getConn();
        List list  =runner.query(conn,sql,new BeanListHandler<User>(User.class));
        conn.close();
        return list;
    }

    @Override
    public int adduid(User user) throws SQLException {
        String sql="INSERT INTO `user` VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
        Connection conn=AliPool.getConn();
        pst = conn.prepareStatement(sql);
        pst.setInt(1,user.getUid());
        pst.setString(2,user.getUusername());
        pst.setString(3,user.getUpass());
        pst.setString(4,user.getUname());
        pst.setString(5,user.getUnumber());
        pst.setString(6,user.getUemile());
        pst.setString(7,user.getUgender());
        pst.setInt(8,user.getUage());
        pst.setString(9,user.getUprofession());
        pst.setString(10,user.getUaddress());
        pst.setString(11,user.getUstate());
        pst.setString(12,user.getUheadx());
        int rs = pst.executeUpdate();
        conn.close();
        return rs;
    }

    @Override
    public int del(int uid) throws SQLException {
        String sql = "DELETE FROM user where uid =?";
        Connection conn=AliPool.getConn();
        int ok = runner.update(conn,sql,uid);
        conn.close();
        return ok;
    }

    @Override
    public int update(User user) throws SQLException {
        Connection conn=AliPool.getConn();
        String sql="update user set uusername=?,upass=?,uname=?,unumber=?,uemile=?," +
                "ugender=?,uage=?,uprofession=?,uaddress=?,ustate=?,uheadx=? where uid=?";
        Object[] us={user.getUusername(),user.getUpass(),user.getUname(),user.getUnumber(),user.getUemile(),
        user.getUgender(),user.getUage(),user.getUprofession(),user.getUaddress(),user.getUstate(),user.getUheadx(),user.getUid()};
        int rs=runner.update(conn,sql,us);
        conn.close();
        return  rs;
    }

    @Override
    public User myup(int uid) throws SQLException {
        Connection conn=AliPool.getConn();
        String sql="select * from `user` where `uid` = ?";
        User ok = runner.query(conn,sql,new BeanHandler<User>(User.class),uid);
        conn.close();
        return ok;
    }

    @Override
    public uclass findclass(int uid) throws SQLException {
        Connection conn=AliPool.getConn();
        String sql="select * from userclass where uid=?";
        uclass fin=runner.query(conn,sql,new BeanHandler<uclass>(uclass.class),uid);
        conn.close();
        return fin;
    }

    @Override
    public List chefind() throws SQLException {
        Connection conn=AliPool.getConn();
        String sql="select * from classes";
        List cfin=runner.query(conn,sql,new BeanListHandler<Classes>(Classes.class));
        conn.close();
        return cfin;
    }

    @Override
    public List<Classes> cfind(String cname) throws SQLException {
        Connection conn=AliPool.getConn();
        List<Classes> clit=new ArrayList<Classes>();
        String sql="select * from classes where cname=?";
        PreparedStatement pst=conn.prepareStatement(sql);
        pst.setString(1,cname);
        ResultSet rs=pst.executeQuery();
        while (rs.next()){
            int cid=rs.getInt(1);
            String cnames=rs.getString(2);
            String cgre=rs.getString(3);
            String cstatic=rs.getString(4);
            Date ctime=rs.getDate(5);
            int teachr=rs.getInt(6);
            Classes classes=new Classes(cid,cnames,cgre,cstatic,ctime,teachr);
            clit.add(classes);
        }
        conn.close();
        return clit;
    }

    @Override
    public int upbj(int uid,String cname) throws SQLException {
        Connection conn = AliPool.getConn();
        String sql="update userclass set cname=? where uid=?";
        int rs = runner.update(conn,sql,cname,uid);
        conn.close();
        return rs;
    }

    @Override
    public int cadd(String cname,String sort,String sta,int cid) throws SQLException {
        Connection conn=AliPool.getConn();
        String sql="insert into classes  values(null,?,?,?,now(),?)";
        Object[] cs={cname,sort,sta,cid};
        int val=runner.update(conn,sql,cs);
        conn.close();
        return val;
    }

    @Override
    public int ucadd(classuser uc) throws SQLException {
        Connection conn=AliPool.getConn();
        String sql="insert into class_user values(null,?,?)";
        Object[] ucs={uc.getClassid(),uc.getUid()};
        int val=runner.update(conn,sql,ucs);
        conn.close();
        return val;
    }

    @Override
    public List<User> uacc(String acc) throws SQLException {
        Connection conn=AliPool.getConn();
        List<User> ulit=new ArrayList<User>();
        String sql="select * from user where uusername=?";
        PreparedStatement pst=conn.prepareStatement(sql);
        pst.setString(1,acc);
        ResultSet rs=pst.executeQuery();
        while (rs.next()){
            int uid=rs.getInt(1);
            String username=rs.getString(2);
            String upass = rs.getString(3);
            String uname = rs.getString(4);
            String unumber = rs.getString(5);
            String uemile = rs.getString(6);
            String ugender = rs.getString(7);
            int uage = rs.getInt(8);
            String uprofession = rs.getString(9);
            String uaddress = rs.getString(10);
            String ustate = rs.getString(11);
            String uheadx = rs.getString(12);
            User user= new User(uid,username,upass,uname,unumber,uemile,ugender,uage,uprofession,uaddress,ustate,uheadx);
            ulit.add(user);
        }
        conn.close();
        return ulit;
    }

    @Override
    public List<Classes> call() throws SQLException {
        Connection conn=AliPool.getConn();
        String sql="select * from classes";
        List<Classes> clist=runner.query(conn,sql,new BeanListHandler<Classes>(Classes.class));
        return clist;
    }

    public static void main(String[] args) {

//        try {
//            List<User> lit=udao.uacc("hh12345");
//            for(User uet:lit){
//                System.out.println(uet.getUid());
//            }
//
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }
//        try {
//            List<Classes> clit=udao.cfind("c1801");
//            for(Classes cs:clit){
//                System.out.println(cs.getCid());
//            }
//
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }
        try {
            boolean bool=cexist("c1801");
            System.out.println(bool);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    protected static boolean cexist(String cname) throws SQLException {
        UserDao udao=new UserDaoImpl();
        List<Classes> clit=udao.call();
        for (Classes cs:clit){
            if (cs.getCname().equals(cname)){
                return  false;
            }
        }
        return true;
    }
}
